# -*- coding: utf-8 -*-
from pydantic import typing
from sqlalchemy import Column, Integer, String, select, ForeignKey, DateTime, func, JSON, text

from app.models.base import Base
from app.schemas.point_management.my_application_schemas import UserApplySchemas, UserApplyDetailSchemas


class UserApply(Base):
    """ 申请表  """
    __tablename__ = 'user_apply'

    name = Column(String(50), nullable=True, comment='申请名称')
    oa_serial_number = Column(String(50), nullable=True, comment='申请单号')

    user_id = Column(Integer, nullable=True, comment='申请人id')
    user_name = Column(String(50), nullable=True, comment='申请人姓名')
    user_number = Column(String(50), nullable=True, comment='申请人工号')
    post_id = Column(Integer, nullable=True, comment='申请人岗位id')
    post_name = Column(String(50), nullable=True, comment='申请人岗位名称')
    department_id = Column(Integer, nullable=True, comment='申请人部门id')
    department_name = Column(String(50), nullable=True, comment='申请人部门名称')

    type = Column(Integer, nullable=True, comment='申请类型 10 工作类, 20 行为类 30 其他类', default=20)
    status = Column(Integer, nullable=True, comment='单据状态 10 审核中, 20 草稿 30 同意 40拒绝 50撤銷', default=20)
    label = Column(String(50), nullable=True, comment='标签')
    image = Column(String(50), nullable=True, comment='上传图片')

    auditor = Column(String(50), nullable=True, comment='审核人')
    authorizer = Column(String(50), nullable=True, comment='授权人')
    award_deduction_date = Column(DateTime(), default=func.now(), onupdate=func.now(), nullable=False,
                                  comment='奖扣日期')
    remark = Column(String(255), nullable=True, comment='备注')

    @classmethod
    async def get_list(cls, params: UserApplySchemas):
        q = [cls.enabled_flag == 1]
        if params.id:
            q.append(cls.id == params.id)
        if params.name:
            q.append(cls.name.like('%{}%'.format(params.name)))
        stmt = select(cls.get_table_columns()) \
            .where(*q) \
            .order_by(cls.id.desc())
        return await cls.pagination(stmt)

    @classmethod
    async def get_user_apply_by_id(cls, params: UserApplySchemas):
        """获取申请id"""
        if params.id:
            stmt = select(cls.get_table_columns()).where(cls.id == params.id, cls.enabled_flag == 1)
            return await cls.get_result(stmt)

    @classmethod
    async def get_user_apply_id(cls, params: UserApplySchemas):
        """根据oa单号获取申请id"""
        stmt = select(cls.id).where(cls.oa_serial_number == params['oa_serial_number'], cls.enabled_flag == 1)
        return await cls.get_result(stmt)


    @classmethod
    def get_user_apply_id_list(cls):
        return cls.query.filter(cls.enabled_flag == 1) \
            .with_entities(cls.id,
                           cls.responsible_name,
                           cls.test_user,
                           cls.dev_user,
                           cls.created_by) \
            .all()


class UserApplyDetail(Base):
    """ 申请详情表  """
    __tablename__ = 'user_apply_detail'

    apply_id = Column(Integer, nullable=True, comment='申请id')
    apply_user_id = Column(Integer, nullable=True, comment='申请人id')
    participant_user_id = Column(Integer, nullable=True, comment='参与人id')
    participant_user_name = Column(String(50), nullable=True, comment='参与人姓名')
    participant_user_number = Column(String(50), nullable=True, comment='参与人工号')
    point_level = Column(Integer, nullable=True, comment='积分等级 10 A+, 20 B+ ', default=10)
    award_deduction_type = Column(Integer, nullable=True, comment='奖扣类型 10 加分, 20 扣分 ', default=10)
    award_deduction_value = Column(Integer, nullable=True, comment='奖扣分数', default=0)

    @classmethod
    async def get_user_apply_detail_list(cls, params: UserApplyDetailSchemas):
        """
          查询所有单据明细
        """
        q = [cls.enabled_flag == 1, UserApply.enabled_flag == 1]
        if params.participant_user_name:
            q.append(cls.participant_user_name.like('%{}%'.format(params.participant_user_name)))
        if params.participant_user_number:
            q.append(cls.participant_user_number.like('%{}%'.format(params.participant_user_number)))
        if params.creation_date:
            q.append(cls.creation_date > params.creation_date)
        if params.updation_date:
            q.append(cls.updation_date > params.updation_date)
        if params.point_level:
            q.append(cls.point_level == params.point_level)
        if params.award_deduction_type:
            q.append(cls.award_deduction_type == params.award_deduction_type)
        stmt = select(cls.get_table_columns(), UserApply.type) \
            .where(*q) \
            .order_by(cls.id.desc())
        return await cls.pagination(stmt)

    @classmethod
    async def get_user_apply_statistics_list(cls, params: UserApplyDetailSchemas):
        """
          查询所有统计
        """
        q = [cls.enabled_flag == 1, UserApply.enabled_flag == 1, UserApply.status == 30]
        if params.participant_user_name:
            q.append(cls.participant_user_name.like('%{}%'.format(params.participant_user_name)))
        if params.participant_user_number:
            q.append(cls.participant_user_number.like('%{}%'.format(params.participant_user_number)))
        if params.creation_date:
            q.append(cls.creation_date > params.creation_date)
        if params.updation_date:
            q.append(cls.updation_date > params.updation_date)
        if params.point_level:
            q.append(cls.point_level == params.point_level)
        if params.award_deduction_type:
            q.append(cls.award_deduction_type == params.award_deduction_type)
        stmt = select(cls.get_table_columns(), UserApply.type,
                      func.sum(cls.award_deduction_value).label('award_deduction_value_sum')) \
            .filter(*q) \
            .outerjoin(UserApply, UserApply.id == cls.apply_id) \
            .group_by(cls.participant_user_number) \
            .group_by(cls.point_level) \
            .group_by(cls.award_deduction_type) \
            .order_by(cls.id.desc())
        return await cls.pagination(stmt)

    @classmethod
    async def get_points_rank(cls, params: UserApplyDetailSchemas):
        """
          积分排行榜
        """
        q = [cls.enabled_flag == 1, UserApply.enabled_flag == 1, UserApply.status == 30, cls.point_level]
        if params.point_level:
            q.append(cls.point_level == params.point_level)
        if params.award_deduction_type:
            q.append(cls.award_deduction_type == params.award_deduction_type)
        stmt = select(cls.get_table_columns(), UserApply.type,
                      func.sum(cls.award_deduction_value).label('award_deduction_value_sum')) \
            .filter(*q) \
            .outerjoin(UserApply, UserApply.id == cls.apply_id) \
            .group_by(cls.participant_user_number) \
            .order_by(cls.id.desc())
        return await cls.pagination(stmt)

    @classmethod
    async def get_user_apply_detail_by_id(cls, apply_id):
        if not apply_id:
            return []
        stmt = select(cls.get_table_columns()).where(cls.apply_id == apply_id, cls.enabled_flag == 1)
        return await cls.get_result(stmt)
